﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using XMK.Framework.DataAccess;
using XMK.Tadong.Model;
using XMK.Utils;
using XMK.Framework.Model;
using XMK.Tadong.Model.Cond;

namespace XMK.Tadong.Data
{
    /// <summary>
    /// 个人共享文档
    /// </summary>
   public  class Tb_Person_File_ShareData
    {
       /// <summary>
        /// 数据库连接
        /// </summary>
        protected DbProvider dbProvider;

        public Tb_Person_File_ShareData(DbProvider dbProvider)
        {
            this.dbProvider = dbProvider;

        }
        /// <summary>
        /// 判断是否已有相同文件
        /// </summary>
        /// <param name="?"></param>
        /// <returns></returns>
        public bool IsExist(TB_PERSON_FILE_SHAREInfo fileShareObj)
        {
            decimal file_id = fileShareObj.FILE_ID;
            decimal share_obj_id = fileShareObj.SHARE_OBJ_ID;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT count(*) FROM TB_PERSON_FILE_SHARE WHERE FILE_ID= '" + file_id + "' and SHARE_OBJ_ID ='" + share_obj_id + "'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            object obj = DbManager.ExecScalar(cmd);
            return (Convert.ToInt16(obj) != 0);
        }
        /// <summary>
        /// 添加新共享文件
        /// </summary>
        /// <returns></returns>
        public bool InsertNewFile(TB_PERSON_FILE_SHAREInfo fileShareObj)
        {
            bool result = true;
            string sql = @"insert into TB_PERSON_FILE_SHARE(SHARE_OBJ_ID , SHARE_OBJ_TYPE ,FILE_ID  ,CREATE_TIME  , UPD_TIME)
                                             values(:p_SHARE_OBJ_ID,:p_SHARE_OBJ_TYPE,:p_FILE_ID,:p_CREATE_TIME, :p_UPD_TIME)";

            //新建一个数据库操作帮助类
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sql,
                //参数生成
                delegate()
                {
                    return DbCmdHelper.CreateParameters(dbProvider, "SHARE_OBJ_ID", "SHARE_OBJ_TYPE", "FILE_ID", "CREATE_TIME", "UPD_TIME").ToArray();
                });
            //参数设值
            cmd.SetParamValues(fileShareObj.SHARE_OBJ_ID, fileShareObj.SHARE_OBJ_TYPE, fileShareObj.FILE_ID, fileShareObj.CREATE_TIME, fileShareObj.UPD_TIME);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            int back = DbManager.ExecNonQuery(cmd);
            if (back == 0)
                result = false;

            return result;

        }
       /// <summary>
       /// 根据课题组ID获得当前课题组共享文件
       /// </summary>
       /// <param name="teamId"></param>
       /// <returns></returns>
        public DataTable getShareFileInfoByTeamId(string teamId, string sorttype, string sortdesc, int startNum, int endNum, ref int total)
        {
            StringBuilder strSql_ = new StringBuilder();
            strSql_.Append("SELECT count(*) FROM TB_PERSON_FILE_SHARE where  SHARE_OBJ_ID='" + teamId + "' and SHARE_OBJ_TYPE=10");
            DbCmdHelper cmd_ = new DbCmdHelper(dbProvider, strSql_.ToString());
            cmd_.DbCmdInfo.CommandType = CommandType.Text;
            object obj = DbManager.ExecScalar(cmd_);

            total = Convert.ToInt32(obj);

            StringBuilder strSql = new StringBuilder();
            if (sorttype == "time")
            {
                if (sortdesc == "desc")
                {
                    strSql.Append("SELECT * FROM (SELECT  ROWNUM AS rowno,FILE_ID,Create_Time,UPD_TIME,FILE_NAME,USER_NAME from(SELECT TB_PERSON_FILE_SHARE.FILE_ID,TB_PERSON_FILE_SHARE.Create_Time,TB_PERSON_FILE_SHARE.UPD_TIME,TB_PERSON_FILE.FILE_NAME,base_userinfo.USER_NAME FROM TB_PERSON_FILE_SHARE，TB_PERSON_FILE，  base_userinfo where TB_PERSON_FILE_SHARE.FILE_ID = TB_PERSON_FILE.FILE_ID and TB_PERSON_FILE.USER_ID=base_userinfo.USER_ID AND  SHARE_OBJ_ID = ' " + teamId + "'and SHARE_OBJ_TYPE=10 ORDER BY TB_PERSON_FILE_SHARE.CREATE_TIME DESC)) table_tmp WHERE table_tmp.rowno >  " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + "");
                }
                else
                {
                    strSql.Append("SELECT * FROM (SELECT  ROWNUM AS rowno,FILE_ID,Create_Time,UPD_TIME,FILE_NAME,USER_NAME from(SELECT TB_PERSON_FILE_SHARE.FILE_ID,TB_PERSON_FILE_SHARE.Create_Time,TB_PERSON_FILE_SHARE.UPD_TIME,TB_PERSON_FILE.FILE_NAME,base_userinfo.USER_NAME FROM TB_PERSON_FILE_SHARE，TB_PERSON_FILE，  base_userinfo where TB_PERSON_FILE_SHARE.FILE_ID = TB_PERSON_FILE.FILE_ID and TB_PERSON_FILE.USER_ID=base_userinfo.USER_ID AND  SHARE_OBJ_ID = ' " + teamId + "'and SHARE_OBJ_TYPE=10 ORDER BY TB_PERSON_FILE_SHARE.CREATE_TIME asc)) table_tmp WHERE table_tmp.rowno >  " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + "");
                }
            }
            else if (sorttype == "user")
            {
                if (sortdesc == "desc")
                {
                    strSql.Append("SELECT * FROM (SELECT  ROWNUM AS rowno,FILE_ID,Create_Time,UPD_TIME,FILE_NAME,USER_NAME from(SELECT TB_PERSON_FILE_SHARE.FILE_ID,TB_PERSON_FILE_SHARE.Create_Time,TB_PERSON_FILE_SHARE.UPD_TIME,TB_PERSON_FILE.FILE_NAME,base_userinfo.USER_NAME FROM TB_PERSON_FILE_SHARE，TB_PERSON_FILE，  base_userinfo where TB_PERSON_FILE_SHARE.FILE_ID = TB_PERSON_FILE.FILE_ID and TB_PERSON_FILE.USER_ID=base_userinfo.USER_ID AND  SHARE_OBJ_ID = ' " + teamId + "'and SHARE_OBJ_TYPE=10 ORDER BY base_userinfo.USER_ID  DESC)) table_tmp WHERE table_tmp.rowno >  " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + "");
                }
                else
                {
                    strSql.Append("SELECT * FROM (SELECT  ROWNUM AS rowno,FILE_ID,Create_Time,UPD_TIME,FILE_NAME,USER_NAME from(SELECT TB_PERSON_FILE_SHARE.FILE_ID,TB_PERSON_FILE_SHARE.Create_Time,TB_PERSON_FILE_SHARE.UPD_TIME,TB_PERSON_FILE.FILE_NAME,base_userinfo.USER_NAME FROM TB_PERSON_FILE_SHARE，TB_PERSON_FILE，  base_userinfo where TB_PERSON_FILE_SHARE.FILE_ID = TB_PERSON_FILE.FILE_ID and TB_PERSON_FILE.USER_ID=base_userinfo.USER_ID AND  SHARE_OBJ_ID = ' " + teamId + "'and SHARE_OBJ_TYPE=10 ORDER BY base_userinfo.USER_ID asc)) table_tmp WHERE table_tmp.rowno >  " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + "");
                }
            }
            
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds != null)
            {
                return ds.Copy();
            }
            return null;
        }
        /// <summary>
        /// 获得项目库共享文件
        /// </summary>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public DataTable getXmkShareFileInfo(int startNum, string sorttype, string sortdesc, int endNum, ref int total)
        {
            StringBuilder strSql_ = new StringBuilder();
            strSql_.Append("SELECT count(*) FROM TB_PERSON_FILE_SHARE where SHARE_OBJ_TYPE=20");
            DbCmdHelper cmd_ = new DbCmdHelper(dbProvider, strSql_.ToString());
            cmd_.DbCmdInfo.CommandType = CommandType.Text;
            object obj = DbManager.ExecScalar(cmd_);

            total = Convert.ToInt32(obj);

            StringBuilder strSql = new StringBuilder();
            if (sorttype == "time")
            {
                if (sortdesc == "desc")
                {
                    strSql.Append("SELECT * FROM (SELECT  ROWNUM AS rowno,FILE_ID,Create_Time,UPD_TIME,FILE_NAME,USER_NAME FROM(SELECT TB_PERSON_FILE_SHARE.FILE_ID,TB_PERSON_FILE_SHARE.Create_Time,TB_PERSON_FILE_SHARE.UPD_TIME,TB_PERSON_FILE.FILE_NAME,base_userinfo.USER_NAME FROM TB_PERSON_FILE_SHARE， TB_PERSON_FILE，  base_userinfo where TB_PERSON_FILE_SHARE.FILE_ID = TB_PERSON_FILE.FILE_ID and TB_PERSON_FILE.USER_ID=base_userinfo.USER_ID and SHARE_OBJ_TYPE=20 ORDER BY TB_PERSON_FILE_SHARE.CREATE_TIME DESC）) table_tmp WHERE table_tmp.rowno >  " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + "");
                }
                else
                {
                    strSql.Append("SELECT * FROM (SELECT  ROWNUM AS rowno,FILE_ID,Create_Time,UPD_TIME,FILE_NAME,USER_NAME FROM(SELECT TB_PERSON_FILE_SHARE.FILE_ID,TB_PERSON_FILE_SHARE.Create_Time,TB_PERSON_FILE_SHARE.UPD_TIME,TB_PERSON_FILE.FILE_NAME,base_userinfo.USER_NAME FROM TB_PERSON_FILE_SHARE，TB_PERSON_FILE，  base_userinfo where TB_PERSON_FILE_SHARE.FILE_ID = TB_PERSON_FILE.FILE_ID and TB_PERSON_FILE.USER_ID=base_userinfo.USER_ID and SHARE_OBJ_TYPE=20 ORDER BY TB_PERSON_FILE_SHARE.CREATE_TIME asc）) table_tmp WHERE table_tmp.rowno >  " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + "");
                }
            }
            else if (sorttype == "user")
            {
                if (sortdesc == "desc")
                {
                    strSql.Append("SELECT * FROM (SELECT  ROWNUM AS rowno,FILE_ID,Create_Time,UPD_TIME,FILE_NAME,USER_NAME FROM((SELECT TB_PERSON_FILE_SHARE.FILE_ID,TB_PERSON_FILE_SHARE.Create_Time,TB_PERSON_FILE_SHARE.UPD_TIME,TB_PERSON_FILE.FILE_NAME,base_userinfo.USER_NAME FROM TB_PERSON_FILE_SHARE，TB_PERSON_FILE，  base_userinfo where TB_PERSON_FILE_SHARE.FILE_ID = TB_PERSON_FILE.FILE_ID and TB_PERSON_FILE.USER_ID=base_userinfo.USER_ID and SHARE_OBJ_TYPE=20 ORDER BY base_userinfo.USER_ID DESC）) table_tmp WHERE table_tmp.rowno >  " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + "");
                }
                else
                {
                    strSql.Append("SELECT * FROM (SELECT  ROWNUM AS rowno,FILE_ID,Create_Time,UPD_TIME,FILE_NAME,USER_NAME FROM(SELECT TB_PERSON_FILE_SHARE.FILE_ID,TB_PERSON_FILE_SHARE.Create_Time,TB_PERSON_FILE_SHARE.UPD_TIME,TB_PERSON_FILE.FILE_NAME,base_userinfo.USER_NAME FROM TB_PERSON_FILE_SHARE，TB_PERSON_FILE，  base_userinfo where TB_PERSON_FILE_SHARE.FILE_ID = TB_PERSON_FILE.FILE_ID and TB_PERSON_FILE.USER_ID=base_userinfo.USER_ID and SHARE_OBJ_TYPE=20 ORDER BY base_userinfo.USER_ID ASC）) table_tmp WHERE table_tmp.rowno >  " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + "");
                }
            }

            
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds != null)
            {
                return ds.Copy();
            }
            return null;
        }
        // <summary>
        /// 获得当前用户所有共享文件
        /// </summary>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public DataTable getShareFileInfoByUserId(string userId, string sorttype, string sortdesc, int startNum, int endNum, ref int total)
        {

            StringBuilder strSql_ = new StringBuilder();
            strSql_.Append("select count(*) FROM TB_PERSON_FILE a join TB_PERSON_FILE_SHARE b on a.FILE_ID = b.FILE_ID where a.User_ID='" + userId + "'");
            DbCmdHelper cmd_ = new DbCmdHelper(dbProvider, strSql_.ToString());
            cmd_.DbCmdInfo.CommandType = CommandType.Text;
            object obj = DbManager.ExecScalar(cmd_);

            total = Convert.ToInt32(obj);

            //获得文件信息和共享表信息
            StringBuilder strSql = new StringBuilder();
            if (sorttype == "time")
            {
                if (sortdesc == "desc")
                {
                    strSql.Append("SELECT * FROM (select ROWNUM AS rowno, FILE_NAME,SHARE_OBJ_ID,FILE_ID,CREATE_TIME FROM (SELECT a.FILE_NAME,b.SHARE_OBJ_ID，b.FILE_ID,b.CREATE_TIME FROM TB_PERSON_FILE a join TB_PERSON_FILE_SHARE b on a.FILE_ID = b.FILE_ID where a.User_ID='" + userId + "' order by b.CREATE_TIME DESC))table_tmp WHERE table_tmp.rowno >   " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + " ");
                }
                else
                {
                    strSql.Append("SELECT * FROM (select ROWNUM AS rowno, FILE_NAME,SHARE_OBJ_ID,FILE_ID,CREATE_TIME FROM (SELECT a.FILE_NAME,b.SHARE_OBJ_ID，b.FILE_ID,b.CREATE_TIME FROM TB_PERSON_FILE a join TB_PERSON_FILE_SHARE b on a.FILE_ID = b.FILE_ID where a.User_ID='" + userId + "' order by b.CREATE_TIME ASC))table_tmp WHERE table_tmp.rowno >   " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + " ");
                }
            }
            else if (sorttype == "space")
            {
                if (sortdesc == "desc")
                {
                    strSql.Append("SELECT * FROM (select ROWNUM AS rowno, FILE_NAME,SHARE_OBJ_ID,FILE_ID,CREATE_TIME FROM (SELECT a.FILE_NAME,b.SHARE_OBJ_ID，b.FILE_ID,b.CREATE_TIME FROM TB_PERSON_FILE a join TB_PERSON_FILE_SHARE b on a.FILE_ID = b.FILE_ID where a.User_ID='" + userId + "' order by b.SHARE_OBJ_ID desc)) table_tmp WHERE table_tmp.rowno >   " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + " ");
                }
                else 
                {
                    strSql.Append("SELECT * FROM (select ROWNUM AS rowno, FILE_NAME,SHARE_OBJ_ID,FILE_ID,CREATE_TIME FROM (SELECT a.FILE_NAME,b.SHARE_OBJ_ID，b.FILE_ID,b.CREATE_TIME FROM TB_PERSON_FILE a join TB_PERSON_FILE_SHARE b on a.FILE_ID = b.FILE_ID where a.User_ID='" + userId + "' order by b.SHARE_OBJ_ID ASC)) table_tmp WHERE table_tmp.rowno >   " + startNum.ToString() + "  and table_tmp.rowno <" + endNum.ToString() + " ");
                }
            }
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            //获得空间信息,name
            ds.Columns.Add("SHARE_OBJ_NAME", typeof(string));
            for (int i = 0; i < ds.Rows.Count; i++)
            {
                //if (ds.Rows[i]["SHARE_OBJ_TYPE"].ToString() == "20")
                //{
                //    ds.Rows[i]["SHARE_OBJ_NAME"] = "项目库";
                //}
                //else {
                    StringBuilder strSql1 = new StringBuilder();
                    strSql1.Append("SELECT TEAM_NAME FROM TB_TEAM WHERE TEAM_ID='"+ds.Rows[i]["SHARE_OBJ_ID"].ToString()+"'");
                    DbCmdHelper cmd1 = new DbCmdHelper(dbProvider, strSql1.ToString());
                    cmd1.DbCmdInfo.CommandType = CommandType.Text;
                    DataTable ds1 = DbHelper.ExecFullDataTable(cmd1.DbProvider, cmd1.DbCmdInfo);

                    ds.Rows[i]["SHARE_OBJ_NAME"] = ds1.Rows[0][0].ToString();
                //}
            }
                if (ds != null)
                {
                    return ds.Copy();
                }
            return null;
        }



        // <summary>
        /// 删除当前用户选中共享文件
        /// </summary>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public bool deleteShareFileInfoByFileId(string fileId,string OBJ_ID)
        {
            bool result = true;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from TB_PERSON_FILE_SHARE where FILE_ID in (" + fileId + ") AND SHARE_OBJ_ID in(" + OBJ_ID + ")");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            int back = DbManager.ExecNonQuery(cmd);
            if (back == 0)
                result = false;

            return result;

        }
        public bool deleteShareFileInfoByFileNameAndPath(string name, string path)
        {
            bool result = true;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from TB_PERSON_FILE_SHARE where FILE_ID IN (select FILE_ID from TB_PERSON_FILE where FILE_NAME='" + name + "' and SAVE_PATH='" + path + "' )");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            int back = DbManager.ExecNonQuery(cmd);
            if (back == 0)
                result = false;

            return result;
        }
        /// <summary>
        /// 判断文件是否已经共享到项目库中
        /// </summary>
        /// <param name="fileid"></param>
        /// <returns></returns>
        public bool hava_shared_xmk(string fileid)
        {
            bool result = false;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT FILE_ID FROM TB_PERSON_FILE_SHARE where FILE_ID='" + fileid + "' AND SHARE_OBJ_TYPE='20'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds.Rows.Count > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 判断文件是否已经共享到课题组中
        /// </summary>
        /// <param name="fileid"></param>
        /// <returns></returns>
        public bool hava_shared_team(string fileid,string teamid)
        {
            bool result = false;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT FILE_ID FROM TB_PERSON_FILE_SHARE where FILE_ID='" + fileid + "' AND SHARE_OBJ_ID='" + teamid + "' AND SHARE_OBJ_TYPE='10'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds.Rows.Count > 0)
                return true;
            else
                return false;
        }

    }
}
